<?php


namespace App\Api\Controllers\Merchant;


use App\Api\Controllers\BaseController;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class WFTBillController extends BaseController
{

    private function getMonthDifference($start, $end)
    {
        $years = date('Y', $end) - date('Y', $start);
        $monthTotal = $years * 12 + date('m', $end) - date('m', $start);
        return $monthTotal;
    }

    public function bill(Request $request)
    {
        try {
            $user = $this->parseToken();
            $merchant_id = $user->merchant_id;
            $page = $request->post('page', 1);
            $limit = $request->post('limit', 10);
            $date = $request->post('date');
            $type = $request->post('type', 'daily');
            if (!$date) {
                $date = implode('  至  ', [date('Y-m-d 00:00:00'), date('Y-m-d H:i:s')]);
            }
            $billType = "%Y-%m-%d";
            if ($type == 'monthly') {
                $billType = "%Y-%m";
            }
            list($startDate, $endDate) = explode('至', $date);
            $startDate = trim($startDate);
            $endDate = trim($endDate);
            $storeList = DB::table('merchant_stores')->join('stores', 'merchant_stores.store_id', '=', 'stores.store_id', 'inner')
                ->where('merchant_stores.merchant_id', $merchant_id)
                ->pluck('stores.store_name', 'stores.store_id');
            if (!$storeList) {
                throw new \Exception("未查找到门店信息");
            }
            $storeList = $storeList->toArray();
            $storeIds = array_keys($storeList);
            $MonthTotal = $this->getMonthDifference(strtotime($startDate), strtotime($endDate));
            $tableName = 'wft_stores_orders_' . date('Ym', strtotime($startDate)) . '_bill';
            $sql = [];
            $sql[] = 'select `shop_id`, sum(order_amount) as order_amount, sum(refund_amount) as refund_amount, sum(receive_fee) as receive_fee, sum(receive_refund_fee) as receive_refund_fee, FROM_UNIXTIME(bill_date, "' . $billType . '") as bill_date from `' . $tableName . '` where `shop_id` in (' . implode(',', $storeIds) . ') and `bill_date` between ' . strtotime($startDate) . ' and ' . strtotime($endDate) . ' group by CONCAT(FROM_UNIXTIME(bill_date, "' . $billType . '"),shop_id)';
//            $query = DB::connection('old_mysql')->table($tableName)
//                ->whereIn('shop_id', $storeIds)
//                ->whereBetween('bill_date', [strtotime($startDate), strtotime($endDate)])
//                ->select([
//                    'shop_id',
//                    DB::raw("sum(order_amount) as order_amount"),
//                    DB::raw("sum(refund_amount) as refund_amount"),
//                    DB::raw("sum(receive_fee) as receive_fee"),
//                    DB::raw("sum(receive_refund_fee) as receive_refund_fee"),
//                    DB::raw('FROM_UNIXTIME(bill_date, "%Y-%m-%d") as bill_date'),
//                ])
//                ->groupBy(DB::raw('CONCAT(FROM_UNIXTIME(bill_date, "%Y%m%d"),shop_id)'));
            for ($i = 0; $i < $MonthTotal; $i++) {
                $num = $i + 1;
                $numTime = strtotime("+" . $num . " month", strtotime($startDate));
                $tableName = 'wft_stores_orders_' . date('Ym', $numTime) . '_bill';
//                $union = DB::connection('old_mysql')->table($tableName)
//                    ->whereIn('shop_id', $storeIds)
//                    ->whereBetween('bill_date', [strtotime($startDate), strtotime($endDate)])
//                    ->select([
//                        'shop_id',
//                        DB::raw("sum(order_amount) as order_amount"),
//                        DB::raw("sum(refund_amount) as refund_amount"),
//                        DB::raw("sum(receive_fee) as receive_fee"),
//                        DB::raw("sum(receive_refund_fee) as receive_refund_fee"),
//                        DB::raw('FROM_UNIXTIME(bill_date, "%Y-%m-%d") as bill_date'),
//                    ])
//                    ->groupBy(DB::raw('CONCAT(FROM_UNIXTIME(bill_date, "%Y%m%d"),shop_id)'));
//                $query = $query->unionAll($union);
                $sql[] = 'select `shop_id`, sum(order_amount) as order_amount, sum(refund_amount) as refund_amount, sum(receive_fee) as receive_fee, sum(receive_refund_fee) as receive_refund_fee, FROM_UNIXTIME(bill_date, "' . $billType . '") as bill_date from `' . $tableName . '` where `shop_id` in (' . implode(',', $storeIds) . ') and `bill_date` between ' . strtotime($startDate) . ' and ' . strtotime($endDate) . ' group by CONCAT(FROM_UNIXTIME(bill_date, "' . $billType . '"),shop_id)';
            }
            if (count($sql) > 1) {
                $sql = '((' . implode(')union all (', $sql) . '))';
            } else {
                $sql = '(' . $sql[0] . ')';
            }
            $result = DB::connection('old_mysql')->table(DB::raw($sql . ' as bill'))->paginate($limit, ['*'], 'page', $page);
            $total = $result->total();
            $list = $result->items();
            foreach ($list as $index => $_obj) {
                $item = (array)$_obj;
                $item['shop_name'] = '';
                if (isset($storeList[$item['shop_id']])) {
                    $item['shop_name'] = $storeList[$item['shop_id']];
                }
                $item['order_amount'] = bcdiv($item['order_amount'], 100, 2);
                $item['refund_amount'] = bcdiv($item['refund_amount'], 100, 2);
                $item['receive_fee'] = bcdiv($item['receive_fee'], 100, 2);
                $item['receive_refund_fee'] = bcdiv($item['receive_refund_fee'], 100, 2);
                $list[$index] = $item;
            }
            return json_encode([
                'code' => 0,
                'msg' => 'Ok',
                'count' => $total,
                'data' => $list
            ]);
        } catch (\Exception $e) {
            return json_encode([
                'code' => 1,
                'msg' => $e->getMessage(),
                'count' => 0,
                'data' => [],
            ]);
        }
    }

    public function billExport(Request $request)
    {
        $fp = false;
        try {
            $currentData = date('YmdHis');
            $user = $this->parseToken();
            $merchant_id = $user->merchant_id;
            $date = $request->post('date');
            $type = $request->post('type', 'daily');
            if (!$date) {
                $date = implode('  至  ', [date('Y-m-d 00:00:00'), date('Y-m-d H:i:s')]);
            }
            $billType = "%Y-%m-%d";
            if ($type == 'monthly') {
                $billType = "%Y-%m";
            }
            list($startDate, $endDate) = explode('至', $date);
            $startDate = trim($startDate);
            $endDate = trim($endDate);
            $storeList = DB::table('merchant_stores')->join('stores', 'merchant_stores.store_id', '=', 'stores.store_id', 'inner')
                ->where('merchant_stores.merchant_id', $merchant_id)
                ->pluck('stores.store_name', 'stores.store_id');
            if (!$storeList) {
                throw new \Exception("未查找到门店信息");
            }
            $storeList = $storeList->toArray();
            $storeIds = array_keys($storeList);
            $MonthTotal = $this->getMonthDifference(strtotime($startDate), strtotime($endDate));
            $tableName = 'wft_stores_orders_' . date('Ym', strtotime($startDate)) . '_bill';
            $query = DB::connection('old_mysql')->table($tableName)
                ->whereIn('shop_id', $storeIds)
                ->whereBetween('bill_date', [strtotime($startDate), strtotime($endDate)])
                ->select([
                    'shop_id',
                    DB::raw("sum(order_amount) as order_amount"),
                    DB::raw("sum(refund_amount) as refund_amount"),
                    DB::raw("sum(receive_fee) as receive_fee"),
                    DB::raw("sum(receive_refund_fee) as receive_refund_fee"),
                    DB::raw('FROM_UNIXTIME(bill_date, "' . $billType . '") as bill_date'),
                ])
                ->groupBy(DB::raw('CONCAT(FROM_UNIXTIME(bill_date, "' . $billType . '"),shop_id)'));
            for ($i = 0; $i < $MonthTotal; $i++) {
                $num = $i + 1;
                $numTime = strtotime("+" . $num . " month", strtotime($startDate));
                $tableName = 'wft_stores_orders_' . date('Ym', $numTime) . '_bill';
                $union = DB::connection('old_mysql')->table($tableName)
                    ->whereIn('shop_id', $storeIds)
                    ->whereBetween('bill_date', [strtotime($startDate), strtotime($endDate)])
                    ->select([
                        'shop_id',
                        DB::raw("sum(order_amount) as order_amount"),
                        DB::raw("sum(refund_amount) as refund_amount"),
                        DB::raw("sum(receive_fee) as receive_fee"),
                        DB::raw("sum(receive_refund_fee) as receive_refund_fee"),
                        DB::raw('FROM_UNIXTIME(bill_date, "' . $billType . '") as bill_date'),
                    ])
                    ->groupBy(DB::raw('CONCAT(FROM_UNIXTIME(bill_date, "' . $billType . '"),shop_id)'));
                $query = $query->unionAll($union);
            }

            $file_name = md5(implode('-', ['billExport', $merchant_id, $billType, $currentData])) . '.csv';
            $file_path = public_path("/uploads/csv/" . $file_name);
            if (file_exists($file_path)) {
                unlink($file_path);
            }
            $fp = fopen($file_path, "a"); // 生成文件
            fputcsv($fp, ['账单日期', '门店名称', '订单总额', '总手续费', '退款总额', '退款总手续费']);
            $query->get()->each(function ($_obj, $index) use ($storeList, $fp) {
                $item = (array)$_obj;
                $shop_name = '';
                if (isset($storeList[$item['shop_id']])) {
                    $shop_name = $storeList[$item['shop_id']];
                }
                fputcsv($fp, [
                    $item['bill_date'],
                    $shop_name,
                    bcdiv($item['order_amount'], 100, 2),
                    bcdiv($item['receive_fee'], 100, 2),
                    bcdiv($item['refund_amount'], 100, 2),
                    bcdiv($item['receive_refund_fee'], 100, 2),
                ]);
            });
            fclose($fp);
            $AccessKeyId = env('ALIOSS_AccessKeyId');
            $AccessKeySecret = env('ALIOSS_AccessKeySecret');
            $endpoint = env('ALIOSS_endpoint');
            $bucket = env('ALIOSS_bucket');
            $content = file_get_contents($file_path);
            $ossClient = new \OSS\OssClient($AccessKeyId, $AccessKeySecret, $endpoint);
            $result = $ossClient->putObject($bucket, 'wft/' . $file_name, $content);
            return json_encode([
                'code' => 0,
                'msg' => 'Ok',
                'data' => [
                    'url' => $result['oss-request-url']
                ]
            ]);
        } catch (\Exception $e) {
            if ($fp) {
                fclose($fp);
            }
            return json_encode([
                'code' => 1,
                'msg' => $e->getMessage(),
                'data' => [],
            ]);
        }
    }
    
    public function statistics(Request $request)
    {
        $user = $this->parseToken();
        $merchant_id = $user->merchant_id;
        $date = $request->post('date');
        $shopId = $request->post('shop_id');
        try {
            if (!$date) {
                $date = implode('  至  ', [date('Y-m-d 00:00:00'), date('Y-m-d H:i:s')]);
            }
            list($startDate, $endDate) = explode('至', $date);
            $startDate = trim($startDate);
            $endDate = trim($endDate);
            $storeList = DB::table('merchant_stores')->join('stores', 'merchant_stores.store_id', '=', 'stores.store_id', 'inner')
                ->where('merchant_stores.merchant_id', $merchant_id)
                ->pluck('stores.store_name', 'stores.store_id');
            if (!$storeList) {
                throw new \Exception("未查找到门店信息");
            }
            $storeList = $storeList->toArray();
            $storeIds = array_keys($storeList);
            if ($shopId) {
                if (!in_array($shopId, $storeIds)) {
                    throw new \Exception("未找到门店信息");
                } else {
                    $storeIds = [$shopId];
                }
            }
            $tableName = 'wft_stores_orders_' . date('Ym', strtotime($startDate)) . '_bill';
            // if ($user->phone == '19810980859') {//采蝶轩
            //     $tableName = 'wft_stores_orders_cakeking_bill';
            // }
            $sql = [];
            $sql[] = 'select sum(order_total) as order_total,sum(order_amount) as order_amount,sum(refund_total) as refund_total,sum(refund_amount) as refund_amount, sum(receive_fee) as receive_fee, sum(receive_refund_fee) as receive_refund_fee, receive_code from ' . $tableName . ' where `shop_id` in (' . implode(',', $storeIds) . ') and `bill_date` between ' . strtotime($startDate) . ' and ' . strtotime($endDate) . ' group by receive_code';
            // if ($user->phone != '19810980859') {
                $MonthTotal = $this->getMonthDifference(strtotime($startDate), strtotime($endDate));
                for ($i = 0; $i < $MonthTotal; $i++) {
                    $num = $i + 1;
                    $numTime = strtotime("+" . $num . " month", strtotime($startDate));
                    $tableName = 'wft_stores_orders_' . date('Ym', $numTime) . '_bill';
                    $sql[] = 'select sum(order_total) as order_total,sum(order_amount) as order_amount,sum(refund_total) as refund_total, sum(refund_amount) as refund_amount, sum(receive_fee) as receive_fee, sum(receive_refund_fee) as receive_refund_fee, receive_code from ' . $tableName . ' where `shop_id` in (' . implode(',', $storeIds) . ') and `bill_date` between ' . strtotime($startDate) . ' and ' . strtotime($endDate) . ' group by receive_code';
                }
            // }
            if (count($sql) > 1) {
                $sql = '((' . implode(')union all (', $sql) . '))';
            } else {
                $sql = '(' . $sql[0] . ')';
            }
            $summary = [
                'balance'=>0,//余额
                'total'=>0,//交易笔数
                'amount'=>0,//交易金额
                'free'=>0,//交易手续费
                'refund_total'=>0,//退款总笔数
                'refund_amount'=>0,//退款总额
                'refund_free'=>0,//退款总手续费
            ];
            $summaryList = [];
            $result = DB::connection('old_mysql')->table(DB::raw($sql . ' as Summary'))->get();
            foreach ($result as $index => $_obj) {
                $item = (array)$_obj;
                $summary['total'] += $item['order_total'];
                $summary['amount'] += $item['order_amount'];
                $summary['free'] += $item['receive_fee'];
                $summary['refund_total'] += $item['refund_total'];
                $summary['refund_amount'] += $item['refund_amount'];
                $summary['refund_free'] += $item['receive_refund_fee'];
                $waysSourceObj = DB::table('store_ways_desc')->where('ways_type', $item['receive_code'])->first(['ways_source', 'ways_desc']);
                if ($waysSourceObj) {
                    $ways_desc = explode('-', $waysSourceObj->ways_desc);
                    if(isset($summaryList[$waysSourceObj->ways_source])){
                        $summaryList[$waysSourceObj->ways_source]['total'] += $item['order_total'];
                        $summaryList[$waysSourceObj->ways_source]['amount'] += $item['order_amount'];
                        $summaryList[$waysSourceObj->ways_source]['free'] += $item['receive_fee'];
                        $summaryList[$waysSourceObj->ways_source]['refund_total'] += $item['refund_total'];
                        $summaryList[$waysSourceObj->ways_source]['refund_amount'] += $item['refund_amount'];
                        $summaryList[$waysSourceObj->ways_source]['refund_free'] += $item['receive_refund_fee'];  
                    }else{
                        $summaryList[$waysSourceObj->ways_source]['title'] = $ways_desc[0];
                        $summaryList[$waysSourceObj->ways_source]['total'] = $item['order_total'];
                        $summaryList[$waysSourceObj->ways_source]['amount'] = $item['order_amount'];
                        $summaryList[$waysSourceObj->ways_source]['free'] = $item['receive_fee'];
                        $summaryList[$waysSourceObj->ways_source]['refund_total'] = $item['refund_total'];
                        $summaryList[$waysSourceObj->ways_source]['refund_amount'] = $item['refund_amount'];
                        $summaryList[$waysSourceObj->ways_source]['refund_free'] = $item['receive_refund_fee'];  
                    }
                }
            }
            return json_encode([
                'code' => 0,
                'msg' => 'Ok',
                'data' => [
                    'summary' => $summary,//汇总
                    'summary_list' => $summaryList,
                ],
            ]);
        } catch (\Exception $e) {
            return json_encode([
                'code' => 1,
                'msg' => $e->getMessage(),
                'count' => 0,
                'data' => [],
            ]);
        }

    }
    
    //对账单导出
    public function statisticsExport(Request $request)
    {
        $fp = false;
        try {
            $currentData = date('YmdHis');
            $user = $this->parseToken();
            $merchant_id = $user->merchant_id;
            $date = $request->post('date');
            $shopId = $request->post('shop_id');
            if (!$date) {
                $date = implode('  至  ', [date('Y-m-d 00:00:00'), date('Y-m-d H:i:s')]);
            }
            $billType = "%Y-%m-%d";
            list($startDate, $endDate) = explode('至', $date);
            $startDate = trim($startDate);
            $endDate = trim($endDate);

            $storeList = DB::table('merchant_stores')->join('stores', 'merchant_stores.store_id', '=', 'stores.store_id', 'inner')
                ->where('merchant_stores.merchant_id', $merchant_id)
                ->pluck('stores.store_name', 'stores.store_id');
            if (!$storeList) {
                throw new \Exception("未查找到门店信息");
            }
            $storeList = $storeList->toArray();
            $storeIds = array_keys($storeList);
            if ($shopId) {
                if (!in_array($shopId, $storeIds)) {
                    throw new \Exception("未找到门店信息");
                } else {
                    $storeIds = [$shopId];
                }
            }

            $tableName = 'wft_stores_orders_' . date('Ym', strtotime($startDate)) . '_bill';
            if ($user->phone == '19810980859') {//采蝶轩
                $tableName = 'wft_stores_orders_cakeking_bill';
            }

            $query = DB::connection('old_mysql')->table($tableName)
                ->whereIn('shop_id', $storeIds)
                ->whereBetween('bill_date', [strtotime($startDate), strtotime($endDate)])
                ->select([
                    DB::raw("sum(order_total) as order_total"),
                    DB::raw("sum(order_amount) as order_amount"),
                    DB::raw("sum(receive_fee) as receive_fee"),
                    DB::raw("sum(refund_total) as refund_total"),
                    DB::raw("sum(refund_amount) as refund_amount"),
                    DB::raw("sum(receive_refund_fee) as receive_refund_fee"),
                    DB::raw('FROM_UNIXTIME(bill_date, "' . $billType . '") as bill_date'),
                ])
                ->groupBy(DB::raw('FROM_UNIXTIME(bill_date, "' . $billType . '")'));

            if ($user->phone != '19810980859') {
                $MonthTotal = $this->getMonthDifference(strtotime($startDate), strtotime($endDate));
                for ($i = 0; $i < $MonthTotal; $i++) {
                    $num = $i + 1;
                    $numTime = strtotime("+" . $num . " month", strtotime($startDate));
                    $tableName = 'wft_stores_orders_' . date('Ym', $numTime) . '_bill';
                    $union = DB::connection('old_mysql')->table($tableName)
                        ->whereIn('shop_id', $storeIds)
                        ->whereBetween('bill_date', [strtotime($startDate), strtotime($endDate)])
                        ->select([
                            DB::raw("sum(order_total) as order_total"),
                            DB::raw("sum(order_amount) as order_amount"),
                            DB::raw("sum(receive_fee) as receive_fee"),
                            DB::raw("sum(refund_total) as refund_total"),
                            DB::raw("sum(refund_amount) as refund_amount"),
                            DB::raw("sum(receive_refund_fee) as receive_refund_fee"),
                            DB::raw('FROM_UNIXTIME(bill_date, "' . $billType . '") as bill_date'),
                        ])
                        ->groupBy(DB::raw('FROM_UNIXTIME(bill_date, "' . $billType . '")'));
                    $query = $query->unionAll($union);
                }
            }

            $file_name = md5(implode('-', ['statisticsExport', $merchant_id, $billType, $currentData])) . '.csv';
            $file_path = public_path("/uploads/csv/" . $file_name);
            if (file_exists($file_path)) {
                unlink($file_path);
            }
            $fp = fopen($file_path, "a"); // 生成文件

            if ($user->phone == '19810980859') {
                fputcsv($fp, ['商户号', '商户名称', '结算日期', '开始时间', '结束时间', '收单订单总笔数', '退单订单总笔数', '应收本金', '手续费', '应退本金', '应退手续费', '实收', '实收净额']);
                $query->get()->each(function ($_obj, $index) use ($storeList, $fp) {
                    $netAmount = $_obj->order_amount - $_obj->receive_fee - $_obj->refund_amount + $_obj->receive_refund_fee;
                    $item = (array)$_obj;
                    fputcsv($fp, [
                        '45436105462C030',
                        '合肥采蝶轩企业管理服务有限公司',
                        date('Y-m-d', strtotime('+1 day', strtotime($item['bill_date']))),
                        $item['bill_date'],
                        $item['bill_date'],
                        $item['order_total'],
                        $item['refund_total'],
                        bcdiv($item['order_amount'], 100, 2),
                        bcdiv($item['receive_fee'], 100, 2),
                        bcdiv($item['refund_amount'], 100, 2),
                        bcdiv($item['receive_refund_fee'], 100, 2),
                        bcdiv($item['order_amount'] - $item['refund_amount'], 100, 2),
                        bcdiv($netAmount, 100, 2),
                    ]);
                });
            } else {
                fputcsv($fp, ['日期', '总笔数', '总流水', '总手续费', '退款笔数', '退款总额', '退款总手续费', '实收总额', '实际净额']);
                $query->get()->each(function ($_obj, $index) use ($storeList, $fp) {
                    $netAmount = $_obj->order_amount - $_obj->receive_fee - $_obj->refund_amount + $_obj->receive_refund_fee;
                    $item = (array)$_obj;
                    fputcsv($fp, [
                        $item['bill_date'],
                        $item['order_total'],
                        bcdiv($item['order_amount'], 100, 2),
                        bcdiv($item['receive_fee'], 100, 2),
                        $item['refund_total'],
                        bcdiv($item['refund_amount'], 100, 2),
                        bcdiv($item['receive_refund_fee'], 100, 2),
                        bcdiv($item['order_amount'] - $item['refund_amount'], 100, 2),
                        bcdiv($netAmount, 100, 2),
                    ]);
                });
            }
            fclose($fp);
            $AccessKeyId = env('ALIOSS_AccessKeyId');
            $AccessKeySecret = env('ALIOSS_AccessKeySecret');
            $endpoint = env('ALIOSS_endpoint');
            $bucket = env('ALIOSS_bucket');
            $content = file_get_contents($file_path);
            $ossClient = new \OSS\OssClient($AccessKeyId, $AccessKeySecret, $endpoint);
            $result = $ossClient->putObject($bucket, 'wft/' . $file_name, $content);
            return json_encode([
                'code' => 0,
                'msg' => 'Ok',
                'data' => [
                    'url' => $result['oss-request-url']
                ]
            ]);
        } catch (\Exception $e) {
            if ($fp) {
                fclose($fp);
            }
            return json_encode([
                'code' => 1,
                'msg' => $e->getMessage(),
                'data' => [],
            ]);
        }
    }
}